﻿Imports Microsoft.VisualBasic
Imports System.ComponentModel
Imports System.Collections.Generic

Namespace My
    <ParseChildren(True)> _
    Public Class TableDataSource
        Inherits MySqlDataSource

        Protected Overrides Function CreateDataSourceView(ByVal viewName As String) As System.Web.UI.WebControls.SqlDataSourceView
            Dim view As MySqlDataSourceView = New MySqlDataSourceView(Me, viewName, Me.Context)
            'Me.CancelSelectOnNullParameter = False
            AddHandler view.PreSelecting, AddressOf Me.MySqlDataSourceView_PreSelecting

            Return view
            'Return MyBase.CreateDataSourceView(viewName)
        End Function

        Private Sub MySqlDataSourceView_PreSelecting(ByVal sender As Object, ByVal e As System.EventArgs)
            RaiseEvent PreSelecting(Me, New EventArgs)
            Me.SelectCommand = Me.BuildSelectQuery()
        End Sub

        Public Function BuildSelectQuery() As String
            Dim sql As SelectStringBuilder

            If Me.SelectQuery.MyTable.TableName <> "" Then
                sql = New SelectStringBuilder(Me.SelectQuery.MyTable.TableName, Me.SelectQuery.MyTable.AliasName)
            End If

            For Each column As MyColumn In Me.SelectQuery.MyColumns
                If column.ColumnList.Length > 0 Then
                    sql.AddColumn(column.ColumnList)
                End If
            Next

            For Each relation As Relation In Me.SelectQuery.Relations
                If relation.RelationType = RelationType.LeftJoin Then
                    sql.LeftJoin(relation.TableName, relation.AliasName, relation.LeftKey, relation.RightKey)
                ElseIf relation.RelationType = RelationType.InnerJoin Then
                    sql.InnerJoin(relation.TableName, relation.AliasName, relation.LeftKey, relation.RightKey)
                End If

                Dim currentRelation As MyDataRelationBase = sql.Relations(sql.Relations.Count - 1)

                For Each condition As Condition In relation.Conditions
                    If TypeOf condition Is ValueCondition Then
                        If condition.LogicalOpField Is MyLogicalOperator.MyAnd Then
                            currentRelation.AddCondition(TryCast(condition, ValueCondition).ColumnName, TryCast(condition, ValueCondition).EqualityOpField, Convert.ChangeType(TryCast(condition, ValueCondition).Value, TryCast(condition, ValueCondition).ValueType))
                        ElseIf condition.LogicalOpField Is MyLogicalOperator.MyOr Then
                            currentRelation.OrCondition(TryCast(condition, ValueCondition).ColumnName, TryCast(condition, ValueCondition).EqualityOpField, Convert.ChangeType(TryCast(condition, ValueCondition).Value, TryCast(condition, ValueCondition).ValueType))
                        End If
                    ElseIf TypeOf condition Is VariableCondition Then
                        Dim valueToCompare As Object = Me.FindParameterValue(TryCast(condition, VariableCondition).VariableName)

                        If valueToCompare = TryCast(condition, VariableCondition).GetEmptyValue() Then
                            Continue For
                        End If

                        If condition.LogicalOpField Is MyLogicalOperator.MyAnd Then
                            currentRelation.AddVarCondition(TryCast(condition, VariableCondition).ColumnName, TryCast(condition, VariableCondition).EqualityOpField, TryCast(condition, VariableCondition).VariableName)
                        ElseIf condition.LogicalOpField Is MyLogicalOperator.MyOr Then
                            currentRelation.OrCondition(TryCast(condition, VariableCondition).ColumnName, TryCast(condition, VariableCondition).EqualityOpField, TryCast(condition, VariableCondition).VariableName)
                        End If
                    End If
                Next
            Next

            For Each condition As Condition In Me.SelectQuery.Conditions
                If TypeOf condition Is ValueCondition Then
                    If condition.LogicalOpField Is MyLogicalOperator.MyAnd Then
                        sql.AddCondition(TryCast(condition, ValueCondition).ColumnName, TryCast(condition, ValueCondition).EqualityOpField, Convert.ChangeType(TryCast(condition, ValueCondition).Value, TryCast(condition, ValueCondition).ValueType))
                    ElseIf condition.LogicalOpField Is MyLogicalOperator.MyOr Then
                        sql.OrCondition(TryCast(condition, ValueCondition).ColumnName, TryCast(condition, ValueCondition).EqualityOpField, Convert.ChangeType(TryCast(condition, ValueCondition).Value, TryCast(condition, ValueCondition).ValueType))
                    End If
                ElseIf TypeOf condition Is VariableCondition Then
                    Dim valueToCompare As Object = Me.FindParameterValue(TryCast(condition, VariableCondition).VariableName)

                    If valueToCompare = TryCast(condition, VariableCondition).GetEmptyValue() Then
                        Continue For
                    End If

                    If condition.LogicalOpField Is MyLogicalOperator.MyAnd Then
                        sql.AddVarCondition(TryCast(condition, VariableCondition).ColumnName, TryCast(condition, VariableCondition).EqualityOpField, TryCast(condition, VariableCondition).VariableName)
                    ElseIf condition.LogicalOpField Is MyLogicalOperator.MyOr Then
                        sql.OrVarCondition(TryCast(condition, VariableCondition).ColumnName, TryCast(condition, VariableCondition).EqualityOpField, TryCast(condition, VariableCondition).VariableName)
                    End If
                End If
            Next

            For Each sortColumn As MySortColumn In Me.SelectQuery.MySortColumns
                sql.AddSortColumn(sortColumn.ColumnName, sortColumn.DirectionField)
            Next

            Return sql.ToString()

        End Function

        Public Function BuildInsertQuery() As String
            Dim sql As InsertStringBuilder

            If Me.InsertQuery.MyTable.TableName <> "" Then
                sql = New InsertStringBuilder(Me.InsertQuery.MyTable.TableName)
            End If

            For Each assignment As MyAssignment In Me.InsertQuery.MyAssgniments
                If TypeOf assignment Is MyColumnAssignment Then
                    sql.AddColumnAssignment(assignment.ColumnName, TryCast(assignment, MyColumnAssignment).SelectColumnName)
                ElseIf TypeOf assignment Is MyValueAssignment Then
                    sql.AddAssignment(assignment.ColumnName, TryCast(assignment, MyValueAssignment).GetValue())
                ElseIf TypeOf assignment Is MyVariableAssignment Then
                    Dim valueToCompare As Object = Me.FindParameterValue(TryCast(assignment, MyVariableAssignment).VariableName)

                    If valueToCompare = TryCast(assignment, MyVariableAssignment).GetEmptyValue() Then
                        Continue For
                    End If

                    sql.AddVarAssignment(assignment.ColumnName, TryCast(assignment, MyVariableAssignment).VariableName)
                End If

            Next

            For Each relation As Relation In Me.SelectQuery.Relations
                If relation.RelationType = RelationType.LeftJoin Then
                    sql.LeftJoin(relation.TableName, relation.AliasName, relation.LeftKey, relation.RightKey)
                ElseIf relation.RelationType = RelationType.InnerJoin Then
                    sql.InnerJoin(relation.TableName, relation.AliasName, relation.LeftKey, relation.RightKey)
                End If

                Dim currentRelation As MyDataRelationBase = sql.Relations(sql.Relations.Count - 1)

                For Each condition As Condition In relation.Conditions
                    If TypeOf condition Is ValueCondition Then
                        sql.AddCondition(condition.LogicalOpField, Me.CreateValueCondition(TryCast(condition, ValueCondition)))
                    ElseIf TypeOf condition Is VariableCondition Then
                        Dim valueToCompare As Object = Me.FindParameterValue(TryCast(condition, VariableCondition).VariableName)

                        If valueToCompare = TryCast(condition, VariableCondition).GetEmptyValue() Then
                            Continue For
                        End If

                        sql.AddCondition(condition.LogicalOpField, Me.CreateVarCondition(TryCast(condition, VariableCondition)))
                    End If
                Next
            Next

            For Each condition As Condition In Me.SelectQuery.Conditions
                If TypeOf condition Is ValueCondition Then
                    sql.AddCondition(condition.LogicalOpField, Me.CreateValueCondition(TryCast(condition, ValueCondition)))
                ElseIf TypeOf condition Is VariableCondition Then
                    Dim valueToCompare As Object = Me.FindParameterValue(TryCast(condition, VariableCondition).VariableName)

                    If valueToCompare = TryCast(condition, VariableCondition).GetEmptyValue() Then
                        Continue For
                    End If

                    sql.AddCondition(condition.LogicalOpField, Me.CreateVarCondition(TryCast(condition, VariableCondition)))
                End If
            Next

            Return sql.ToString()

        End Function

        Public Function FindParameterValue(ByVal name As String) As Object
            Dim values As IOrderedDictionary = Me.SelectParameters.GetValues(Me.Context, Me)
            Dim i As Integer
            For i = 0 To Me.SelectParameters.Count - 1
                If Me.SelectParameters(i).Name = name Then
                    Return values(i)
                End If
            Next
        End Function

        Protected Function CreateValueCondition(ByVal condition As ValueCondition) As MyEqualityCondition
            Return New MyEqualityCondition(New MyColumnExpression(condition.ColumnName), condition.EqualityOpField, New MyValueExpression(condition.GetValue()))
        End Function

        Protected Function CreateVarCondition(ByVal condition As VariableCondition) As MyEqualityCondition
            Return New MyEqualityCondition(New MyColumnExpression(condition.ColumnName), condition.EqualityOpField, New MyVariableExpression(condition.VariableName))
        End Function

        Public Property RequireAuth() As Boolean
            Get
                If ViewState("RequireAuth") Is Nothing Then
                    Return False
                End If

                Return ViewState("RequireAuth")
            End Get
            Set(ByVal value As Boolean)
                ViewState("RequireAuth") = value
            End Set
        End Property

        Public Property TableName() As String
            Get
                Return ViewState("TableName")
            End Get
            Set(ByVal value As String)
                ViewState("TableName") = value
            End Set
        End Property

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property SelectQuery() As SelectQuery
            Get
                If ViewState("SelectQuery") Is Nothing Then
                    ViewState("SelectQuery") = New SelectQuery
                End If

                Return ViewState("SelectQuery")
            End Get
        End Property

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property InsertQuery() As InsertQuery
            Get
                If ViewState("InsertQuery") Is Nothing Then
                    ViewState("InsertQuery") = New InsertQuery
                End If

                Return ViewState("InsertQuery")
            End Get
        End Property

        '        <PersistenceMode(PersistenceMode.InnerProperty)> _
        'Public ReadOnly Property Conditions() As List(Of Condition)
        '            Get
        '                If ViewState("Conditions") Is Nothing Then
        '                    ViewState("Conditions") = New List(Of Condition)
        '                End If

        '                Return ViewState("Conditions")
        '            End Get
        '        End Property

        <TypeConverter(GetType(StringArrayConverter)), DefaultValue(GetType(String), Nothing)> _
        Public Property ColumnNames() As String()
            Get
                If ViewState("ColumnNames") Is Nothing Then
                    ViewState("ColumnNames") = New String() {}
                End If

                Return ViewState("ColumnNames")
            End Get
            Set(ByVal value As String())
                ViewState("ColumnNames") = value.Clone()
            End Set
        End Property

        <TypeConverter(GetType(StringArrayConverter)), DefaultValue(GetType(String), Nothing)> _
        Public Property WhereColumnNames() As String()
            Get
                If ViewState("WhereColumnNames") Is Nothing Then
                    ViewState("WhereColumnNames") = New String() {}
                End If

                Return ViewState("WhereColumnNames")
            End Get
            Set(ByVal value As String())
                ViewState("WhereColumnNames") = value.Clone()
            End Set
        End Property

        Private Sub TableDataSource_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
            If Me.EnableSelectOnNullParameter = True Then
                Me.CancelSelectOnNullParameter = False
            End If
        End Sub

        Public Event PreSelecting(ByVal sender As Object, ByVal e As EventArgs)

        Public Property EnableSelectOnNullParameter() As Boolean
            Get
                If ViewState("EnableSelectOnNullParameter") Is Nothing Then
                    ViewState("EnableSelectOnNullParameter") = True
                End If
                Return ViewState("EnableSelectOnNullParameter")
            End Get
            Set(ByVal value As Boolean)
                ViewState("EnableSelectOnNullParameter") = value
            End Set
        End Property
    End Class

End Namespace

Namespace MyWebControls

    <ParseChildren(True)> _
    Public Class MyQuery
        Public MyTableField As New MyTable

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public Property MyTable() As MyTable
            Get
                Return Me.MyTableField
            End Get
            Set(ByVal value As MyTable)
                Me.MyTableField = value
            End Set
        End Property

        Public RelationsField As New List(Of Relation)

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property Relations() As List(Of Relation)
            Get
                Return Me.RelationsField
            End Get
        End Property

        Public ConditionsField As New List(Of Condition)
    End Class

    <ParseChildren(True)> _
    Public Class SelectQuery
        Inherits MyQuery

        Public MyColumnsField As New List(Of MyColumn)

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property MyColumns() As List(Of MyColumn)
            Get
                Return Me.MyColumnsField
            End Get
        End Property

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property Conditions() As List(Of Condition)
            Get
                Return Me.ConditionsField
            End Get
        End Property

        Public MySortColumnsField As New List(Of MySortColumn)

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property MySortColumns() As List(Of MySortColumn)
            Get
                Return Me.MySortColumnsField
            End Get
        End Property
    End Class

    Public Class InsertQuery
        Inherits MyQuery

        Public MyAssignmentsField As New List(Of MyAssignment)

        Public ReadOnly Property MyAssgniments() As List(Of MyAssignment)
            Get
                Return Me.MyAssignmentsField
            End Get
        End Property

        Public MySelectTableField As New MyTable

        Public Property MySelectTable() As MyTable
            Get
                Return Me.MySelectTableField
            End Get
            Set(ByVal value As MyTable)
                Me.MySelectTableField = value
            End Set
        End Property
    End Class

    Public Class MyTable
        Public AliasNameField As String

        Public Property AliasName() As String
            Get
                Return Me.AliasNameField
            End Get
            Set(ByVal value As String)
                Me.AliasNameField = value
            End Set
        End Property

        Public TableNameField As String

        Public Property TableName() As String
            Get
                Return Me.TableNameField
            End Get
            Set(ByVal value As String)
                Me.TableNameField = value
            End Set
        End Property
    End Class

    Public Class MyColumn
        Public AliasNameField As String

        Public Property AliasName() As String
            Get
                Return Me.AliasNameField
            End Get
            Set(ByVal value As String)
                Me.AliasNameField = value
            End Set
        End Property

        Public ColumnNameField As String

        Public Property ColumnName() As String
            Get
                Return Me.ColumnNameField
            End Get
            Set(ByVal value As String)
                Me.ColumnNameField = value
            End Set
        End Property

        Public ColumnListField() As String

        <TypeConverter(GetType(StringArrayConverter)), DefaultValue(GetType(String), Nothing)> _
        Public Property ColumnList() As String()
            Get
                Return Me.ColumnListField
            End Get
            Set(ByVal value() As String)
                Me.ColumnListField = value.Clone()
            End Set
        End Property
    End Class

    Public Class MyAssignment
        Public ColumnNameField As String

        Public Property ColumnName() As String
            Get
                Return Me.ColumnNameField
            End Get
            Set(ByVal value As String)
                Me.ColumnNameField = value
            End Set
        End Property

    End Class

    Public Class MyColumnAssignment
        Inherits MyAssignment

        Public SelectColumnNameField As String

        Public Property SelectColumnName() As String
            Get
                Return Me.SelectColumnNameField
            End Get
            Set(ByVal value As String)
                Me.SelectColumnNameField = value
            End Set
        End Property
    End Class

    Public Class MyValueAssignment
        Inherits MyAssignment

        Public ValueField As String
        Public ValueTypeField As TypeCode = TypeCode.String

        Public Property Value() As String
            Get
                Return Me.ValueField
            End Get
            Set(ByVal value As String)
                Me.ValueField = value
            End Set
        End Property

        Public Property ValueType() As TypeCode
            Get
                Return Me.ValueTypeField
            End Get
            Set(ByVal value As TypeCode)
                Me.ValueTypeField = value
            End Set
        End Property

        Public Function GetValue() As Object
            Return Convert.ChangeType(Me.Value, Me.ValueType)
        End Function
    End Class

    Public Class MyVariableAssignment
        Inherits MyAssignment

        Public VariableNameField As String

        Public Property VariableName() As String
            Get
                Return Me.VariableNameField
            End Get
            Set(ByVal value As String)
                Me.VariableNameField = value
            End Set
        End Property

        Public EmptyValueField As String

        Public Property EmptyValue() As String
            Get
                Return Me.EmptyValueField
            End Get
            Set(ByVal value As String)
                Me.EmptyValueField = value
            End Set
        End Property

        Public EmptyValueTypeField As TypeCode = TypeCode.String

        Public Property EmptyValueType() As TypeCode
            Get
                Return Me.EmptyValueTypeField
            End Get
            Set(ByVal value As TypeCode)
                Me.EmptyValueTypeField = value
            End Set
        End Property

        Public Function GetEmptyValue() As Object
            Return Convert.ChangeType(Me.EmptyValue, Me.EmptyValueType)
        End Function
    End Class

    'Relation
    <ParseChildren(True)> _
    Public Class Relation
        Public AliasNameField As String

        Public Property AliasName() As String
            Get
                Return Me.AliasNameField
            End Get
            Set(ByVal value As String)
                Me.AliasNameField = value
            End Set
        End Property

        Public RelationTypeField As RelationType

        Public Property RelationType() As RelationType
            Get
                Return Me.RelationTypeField
            End Get
            Set(ByVal value As RelationType)
                Me.RelationTypeField = value
            End Set
        End Property

        Public TableNameField As String
        Public LeftKeyField As String
        Public RightKeyField As String

        Public Property TableName() As String
            Get
                Return Me.TableNameField
            End Get
            Set(ByVal value As String)
                Me.TableNameField = value
            End Set
        End Property

        Public Property LeftKey() As String
            Get
                Return Me.LeftKeyField
            End Get
            Set(ByVal value As String)
                Me.LeftKeyField = value
            End Set
        End Property

        Public Property RightKey() As String
            Get
                Return Me.RightKeyField
            End Get
            Set(ByVal value As String)
                Me.RightKeyField = value
            End Set
        End Property

        Public ConditionsField As New List(Of Condition)

        <PersistenceMode(PersistenceMode.InnerProperty)> _
        Public ReadOnly Property Conditions() As List(Of Condition)
            Get
                Return Me.ConditionsField
            End Get
        End Property
    End Class

    Public Class InnerJoin
        Inherits Relation

        Public Sub New()
            Me.RelationType = MyWebControls.RelationType.InnerJoin
        End Sub
    End Class

    Public Class LeftJoin
        Inherits Relation

        Public Sub New()
            Me.RelationType = MyWebControls.RelationType.LeftJoin
        End Sub
    End Class

    Public Enum RelationType
        LeftJoin
        InnerJoin
    End Enum

    'Condition
    Public MustInherit Class Condition
        Public LogicalOpField As MyLogicalOperator = MyLogicalOperator.MyAnd
        Public Shared LogicalOpDict As New Dictionary(Of LogicalOperatorEnum, MyLogicalOperator)

        Shared Sub New()
            LogicalOpDict.Add(LogicalOperatorEnum.MyAnd, MyLogicalOperator.MyAnd)
            LogicalOpDict.Add(LogicalOperatorEnum.MyOr, MyLogicalOperator.MyOr)
        End Sub

        Public WriteOnly Property LogicalOperator() As LogicalOperatorEnum
            Set(ByVal value As LogicalOperatorEnum)
                Me.LogicalOpField = LogicalOpDict(value)
            End Set
        End Property

        Public ColumnNameField As String

        Public Property ColumnName() As String
            Get
                Return Me.ColumnNameField
            End Get
            Set(ByVal value As String)
                Me.ColumnNameField = value
            End Set
        End Property
    End Class

    Public Class EqualityCondition
        Inherits Condition

        Public EqualityOpField As MyEqualityOperator = MyEqualityOperator.Equal
        Public Shared EqualityOpDict As New Dictionary(Of EqualityOperatorEnum, MyEqualityOperator)

        Shared Sub New()
            EqualityOpDict.Add(EqualityOperatorEnum.Equal, MyEqualityOperator.Equal)
            EqualityOpDict.Add(EqualityOperatorEnum.NotEqual, MyEqualityOperator.NotEqual)
            EqualityOpDict.Add(EqualityOperatorEnum.MyLike, MyEqualityOperator.MyLike)
            EqualityOpDict.Add(EqualityOperatorEnum.GreaterThan, MyEqualityOperator.GreaterThan)
            EqualityOpDict.Add(EqualityOperatorEnum.GreaterThanEqual, MyEqualityOperator.GreaterThanEqual)
            EqualityOpDict.Add(EqualityOperatorEnum.LessThan, MyEqualityOperator.LessThan)
            EqualityOpDict.Add(EqualityOperatorEnum.LessThanEqual, MyEqualityOperator.LessThanEqual)
        End Sub

        Public WriteOnly Property EqualityOperator() As EqualityOperatorEnum
            Set(ByVal value As EqualityOperatorEnum)
                Me.EqualityOpField = EqualityOpDict(value)
            End Set
        End Property
    End Class

    Public Class ValueCondition
        Inherits EqualityCondition

        Public ValueField As String
        Public ValueTypeField As TypeCode = TypeCode.String

        Public Property Value() As String
            Get
                Return Me.ValueField
            End Get
            Set(ByVal value As String)
                Me.ValueField = value
            End Set
        End Property

        Public Property ValueType() As TypeCode
            Get
                Return Me.ValueTypeField
            End Get
            Set(ByVal value As TypeCode)
                Me.ValueTypeField = value
            End Set
        End Property

        Public Function GetValue() As Object
            Return Convert.ChangeType(Me.Value, Me.ValueType)
        End Function
    End Class

    Public Class VariableCondition
        Inherits EqualityCondition

        Public VariableNameField As String

        Public Property VariableName() As String
            Get
                Return Me.VariableNameField
            End Get
            Set(ByVal value As String)
                Me.VariableNameField = value
            End Set
        End Property

        Public EmptyValueField As String

        Public Property EmptyValue() As String
            Get
                Return Me.EmptyValueField
            End Get
            Set(ByVal value As String)
                Me.EmptyValueField = value
            End Set
        End Property

        Public EmptyValueTypeField As TypeCode = TypeCode.String

        Public Property EmptyValueType() As TypeCode
            Get
                Return Me.EmptyValueTypeField
            End Get
            Set(ByVal value As TypeCode)
                Me.EmptyValueTypeField = value
            End Set
        End Property

        Public Function GetEmptyValue() As Object
            Return Convert.ChangeType(Me.EmptyValue, Me.EmptyValueType)
        End Function
    End Class

    Public Class MySortColumn
        Public Shared SortDirectionDict As New Dictionary(Of SortDirection, MyDataSortDirection)

        Shared Sub New()
            SortDirectionDict.Add(SortDirection.Ascending, MyDataSortDirection.Ascending)
            SortDirectionDict.Add(SortDirection.Descending, MyDataSortDirection.Descending)
        End Sub

        Public ColumnNameField As String

        Public Property ColumnName() As String
            Get
                Return Me.ColumnNameField
            End Get
            Set(ByVal value As String)
                Me.ColumnNameField = value
            End Set
        End Property

        Public DirectionField As MyDataSortDirection = MyDataSortDirection.Ascending

        Public WriteOnly Property Direction() As SortDirection
            Set(ByVal value As SortDirection)
                Me.DirectionField = SortDirectionDict(value)
            End Set
        End Property
    End Class

    Public Enum LogicalOperatorEnum
        MyAnd
        MyOr
    End Enum

    Public Enum EqualityOperatorEnum
        Equal
        NotEqual
        MyLike
        GreaterThan
        GreaterThanEqual
        LessThan
        LessThanEqual
    End Enum
End Namespace